# -*- coding: utf-8 -*-
#coding=utf-8

import web
import os
import config
from action.admin.base import adminAction

'''
User controller: producing install views.
'''
class refererAction(adminAction):
    def __init__(self, name = '安装系统'):
        adminAction.__init__(self, name, chkInstall=False)
        self.schemas = {
            # status table
            "status":

            """
            CREATE TABLE 'status' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              text VARCHAR(16)
            );
            """,

            # gender table
            "gender": 

            """
            CREATE TABLE 'gender' (
              id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
              text VARCHAR(16)
            );
            """,

            # image table
            "image": 

            """
            CREATE TABLE 'image' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              uuid VARCHAR(128) UNIQUE,
              ref VARCHAR(1024),
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              desc TEXT,
              thumbnail TEXT,
              owner INTEGER DEFAULT 0 REFERENCES user(id) ON DELETE SET DEFAULT,
              status INTEGER DEFAULT 1,
              album INTEGER DEFAULT 1 REFERENCES album(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(status) REFERENCES status(id)
            );
            """,

           # audio table
            "audio": 

            """
            CREATE TABLE 'audio' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              uuid VARCHAR(128) UNIQUE,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              title VARCHAR(128) CHECK(length(title)>1),  
              artist VARCHAR(128),  
              desc TEXT,
              status INTEGER DEFAULT 1,
              thumbnail INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT,
              album INTEGER DEFAULT 1 REFERENCES album(id) ON DELETE SET DEFAULT,
              owner INTEGER DEFAULT 0 REFERENCES user(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(status) REFERENCES status(id)
            );
            """,

           # video table
            "video": 

            """
            CREATE TABLE 'video' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              uuid VARCHAR(128) UNIQUE,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              title VARCHAR(256) CHECK(length(title)>1),  
              duration INT DEFAULT 0,
              width INT DEFAULT 0,
              height INT DEFAULT 0,  
              desc TEXT,
              status INTEGER DEFAULT 1,
              thumbnail TEXT,
              screenshot BLOB,
              album INTEGER DEFAULT 1 REFERENCES album(id) ON DELETE SET DEFAULT,
              owner INTEGER DEFAULT 0 REFERENCES user(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(status) REFERENCES status(id)
            );
            """,

            # product table
            "product":

            """
            CREATE TABLE 'product' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name VARCHAR(256) CHECK(length(name)>1),
              price FLOAT,
              discount FLOAT,
              desc TEXT CHECK(length(desc)>1),
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              telephone VARCHAR(64),
              contact VARCHAR(256),
              email TEXT,
              longitude INT,
              latitude INT,
              region VARCHAR(256),
              address VARCHAR(256),
              rank INTEGER DEFAULT 0,
              owner INTEGER DEFAULT 0 REFERENCES user(id) ON DELETE SET DEFAULT,
              picture INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT,
              category INTEGER DEFAULT 1 REFERENCES category(id) ON DELETE SET DEFAULT,
              company INTEGER DEFAULT 1 REFERENCES company(id) ON DELETE SET DEFAULT
            );
            """,

            # comment table
            "prodcomment": 

            """
            CREATE TABLE 'prodcomment' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              content TEXT,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              rank INTEGER DEFAULT 0 CHECK(rank<100),
              user INTEGER DEFAULT 0,
              product INTEGER DEFAULT 0,
              FOREIGN KEY(user) REFERENCES user(id),
              FOREIGN KEY(product) REFERENCES product(id)
            );
            """,

            # news table
            "news":

            """
            CREATE TABLE 'news' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name VARCHAR(128) CHECK(length(name)>1),
              desc TEXT,
              content TEXT,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              status INTEGER DEFAULT 1,
              thumbnail INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT,
              category INTEGER DEFAULT 1 REFERENCES category(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(status) REFERENCES status(id)
            );
            """,


            # Comment table for news
            "newscomment":

            """
            CREATE TABLE 'newscomment' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              userid INTEGER DEFAULT 0,
              news INTEGER DEFAULT 0,
              content TEXT,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              level INTEGER,
              FOREIGN KEY(userid) REFERENCES user(id),
              FOREIGN KEY(news) REFERENCES news(id)
            );
            """,

            # info table
            "info":

            """
            CREATE TABLE 'info' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name VARCHAR(128) CHECK(length(name)>1),
              desc TEXT,
              content TEXT,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              status INTEGER DEFAULT 1,
              owner INTEGER DEFAULT 0,
              thumbnail INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT,
              category INTEGER DEFAULT 1 REFERENCES category(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(owner) REFERENCES user(id),
              FOREIGN KEY(status) REFERENCES status(id)
            );
            """,

            # Company table
            "company":

            """
            CREATE TABLE 'company' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name VARCHAR(256) CHECK(length(name)>1),
              address VARCHAR(512) CHECK(length(address)>1),
              cellphone VARCHAR(64),
              fixedphone VARCHAR(64),
              faxno VARCHAR(64),
              postcode VARCHAR(64),
              contact VARCHAR(64),
              desc TEXT,
              email TEXT UNIQUE CHECK(email like '_%@__%.__%'),
              owner INTEGER DEFAULT 0,
              logo INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT,
              category INTEGER DEFAULT 1 REFERENCES category(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(owner) REFERENCES user(id)
            );
            """,

            # Comment table for company
            "companycomment":

            """
            CREATE TABLE 'companycomment' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              userid INTEGER DEFAULT 0,
              company INTEGER DEFAULT 0,
              content TEXT,
              createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              level INTEGER,
              FOREIGN KEY(userid) REFERENCES user(id),
              FOREIGN KEY(company) REFERENCES company(id)
            );
            """,

            # User table 
            "user":

            """
            CREATE TABLE 'user' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              email TEXT UNIQUE CHECK(email like '_%@__%.__%'),
              nickname VARCHAR(128) UNIQUE CHECK(length(nickname)>1),
              name VARCHAR(64),
              gender INTEGER DEFAULT 1,
              passwd VARCHAR(64) CHECK(length(passwd)>1),
              lastLoginTime TIMESTAMP,
              address VARCHAR(256),
              phoneno VARCHAR(64),
              rank INTEGER DEFAULT 0 CHECK(rank<100),
              avatur INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT,
              FOREIGN KEY(gender) REFERENCES gender(id)
            );
            """,


            # favorite table
            "favorite": 

            """
            CREATE TABLE 'favorite' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              user INTEGER DEFAULT 0 REFERENCES user(id) ON DELETE CASCADE,
              product INTEGER DEFAULT 0 REFERENCES product(id) ON DELETE CASCADE
            );
            """,


            # Category table
            "category":

            """
            CREATE TABLE 'category' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              seq INTEGER DEFAULT 0,
              name VARCHAR(128) CHECK(length(name)>1),
              desc TEXT,
              thumbnail INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT
            );
            """,

            # Album table
            "album":

            """
            CREATE TABLE 'album' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name VARCHAR(128) CHECK(length(name)>1),
              owner INTEGER DEFAULT 0 REFERENCES user(id) ON DELETE SET DEFAULT,
              desc TEXT CHECK(length(desc)>1)
            );
            """,

            # Advertisement table
            "ad":

            """
            CREATE TABLE 'ad' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              category INTEGER DEFAULT 1,
              album INTEGER DEFAULT 1,
              FOREIGN KEY(category) REFERENCES category(id),
              FOREIGN KEY(album) REFERENCES album(id)
            );
            """,
            
            # About table
            "about":

            """
            CREATE TABLE 'about' (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              company VARCHAR(256) CHECK(length(company)>1),
              address VARCHAR(512) CHECK(length(address)>1),
              phoneno VARCHAR(64),
              faxno VARCHAR(64),
              postcode VARCHAR(64),
              contact VARCHAR(64),
              desc TEXT,
              weibo TEXT,
              weixin TEXT,
              email TEXT UNIQUE CHECK(email like '_%@__%.__%'),
              logo INTEGER DEFAULT 1 REFERENCES image(id) ON DELETE SET DEFAULT
            );
            """,

        }
        #tables columns, not include foreign keys
        self.table_org = {
          "about_org" :         " id,company,address,phoneno,faxno,postcode,contact,desc,weibo,weixin,email ",
          "company_org":        " id,name,address,cellphone,fixedphone,faxno,postcode,contact, desc,email ",
          "info_org":           " id,name,desc,content,createTime,status,owner,thumbnail,category ",
          "status_org":         " id,text",
          "ad_org":             " id",
          "news_org":           " id,name,desc,content,createTime ",
          "user_org":           " id,email,nickname,name,passwd,lastLoginTime,address,phoneno,rank ",
          "album_org":          " id,name,owner,desc ",
          "gender_org":         " id,text ",
          "category_org":       " id,seq,name,desc",
          "image_org":          " id,uuid,ref,createTime,desc,thumbnail",
          "product_org":        " id,name,price,discount,desc,createTime,telephone,contact,email,longitude,latitude,region,address,rank",
          "companycomment_org": " id, content, createTime, level ",
          "favorite_org":       " id, user, product ",
          "info_org":           " id, name, desc, content, createTime ",
          "newscomment_org":    " id, content, createTime,level ",
          "prodcomment_org":    " id, content, createTime,rank ",
          "audio_org":          " id, uuid, createTime,title, artist, desc",
          "video_org":          " id, uuid, createTime,title, duration,width,height,desc, thumbnail, screenshot",
        }

    
    def reinstall(self):
        try:
            import shutil
            # reset upload dir
            if os.path.exists(config.UPLOAD_DIR):
                shutil.rmtree(config.UPLOAD_DIR)
            os.makedirs(config.UPLOAD_DIR)

            # create sessions dir recrusively
            if not os.path.exists(os.path.join(config.DATA_DIR, 'sessions')):
                os.makedirs(os.path.join(config.DATA_DIR, 'sessions'))

            # remove database
            if os.path.exists(self.database):
                os.unlink(self.database)

            # init tables and records
            self.createTables(self.schemas.keys())
            self.createMandatoryRecords()
            return self.success(msg = '系统重新安装成功.', url=self.makeUrl('/admin'))
        except Exception, e:
            return self.error(msg = '系统重新安装失败：%s' % e)

    def upgrade(self):
        if not self.isInstalled():
            return self.error("系统未安装!")

        tables = self.schemas.keys()

        self.backupTables(tables)
        self.dropTables(tables)
        self.createTables(tables)
        self.restoreDataFromBackup(tables)
        self.backupOrgTables()
        self.dropTables(self.table_org.keys())

        return self.success(msg = '系统升级成功.', url=self.makeUrl('/admin'))

    def freshinstall(self):
        if self.isInstalled():
            return self.error("系统已经安装，请选择升级或者重新安装!")

        if not os.path.exists(os.path.join(config.DATA_DIR, 'sessions')):
            os.makedirs(os.path.join(config.DATA_DIR, 'sessions'))

        if not os.path.exists(config.UPLOAD_DIR):
            os.makedirs(config.UPLOAD_DIR)

        self.createTables(self.schemas.keys())
        self.createMandatoryRecords()
        return self.success(msg = '系统安装成功.', url=self.makeUrl('/admin'))

    def executeSQL(self, sqlList):
        import sqlite3 as db
        conn = db.connect(self.database)
        cursor = conn.cursor()

        for sql in sqlList:
            #print sql
            cursor.execute(sql)
        conn.commit()
        conn.close()

    def createTables(self, tables=[]):
        sqlList = []

        for tbl in tables:
            if self.schemas.has_key(tbl):
                sqlList.append(self.schemas[tbl])

        self.executeSQL(sqlList)

    def dropTables(self, tables=[]):
        sqlList = []

        for tbl in tables:
            sqlList.append("DROP table " + tbl)

        self.executeSQL(sqlList)

    def backupTables(self, tables=[]):
        sqlList = []

        for tbl in tables:
            if self.schemas.has_key(tbl):
                sqlList.append("CREATE TABLE " + tbl + "_org " + "as SELECT * FROM " + tbl)
        self.executeSQL(sqlList)

    def backupOrgTables(self):
        import time
        sqlList = []
        currentDate = time.strftime("%Y_%m_%d_%H_%M_%S",time.localtime())

        for tbl in self.table_org.keys():
            sqlList.append("CREATE TABLE " + tbl + "_" + currentDate + " as SELECT * FROM " + tbl)

        self.executeSQL(sqlList)

    def restoreDataFromBackup(self,tables):
        sqlList = []
        for tbl in tables:
            if self.table_org.has_key(tbl+"_org"):
                sqlList.append("insert into " + tbl +"(" + self.table_org[tbl+"_org"] + ")" \
                   +" select " + self.table_org[tbl+"_org"] + " from " + tbl+"_org")
        self.executeSQL(sqlList)

    def createMandatoryRecords(self): 
        condition = {'id': 1}

        # insert status records if required
        from model.status import status
        if not status().getOne('id', condition):
            status().insert({'id': 0, 'text': '草稿'})
            status().insert({'id': 1, 'text': '发布'})

        # insert gender records if required
        from model.gender import gender
        if not gender().getOne('id', condition):
            gender().insert({'id': 0, 'text': '男'})
            gender().insert({'id': 1, 'text': '女'})

        from imaging import imaging
        from model.category import category
        from model.album import album
        from model.image import image
        from model.user import user
        from model.about import about
        from utils import uuidgen

        # initialize default album record if it is not existing
        if not album().getOne('id', condition):
            sqlList = [
                'INSERT INTO album (id, name, desc) VALUES(1, "系统专辑", "系统默认专辑，不允许删除");',
                'INSERT INTO album (id, name, desc) VALUES(2, "滚动广告", "滚动广告专辑，不允许删除");',
            ]
            self.executeSQL(sqlList)

        # initialize default image record if it is not existing
        if not image().getOne('id', condition):
            sqlList = [
                'INSERT INTO image (id, album) VALUES(1, 1);',
            ]
            self.executeSQL(sqlList)

        # initialize default user
        if not user().getOne('id', {'id': 0}):
            user().insert({'id': 0, 'name': 'admin', 'nickname': 'admin' ,'email': 'admin@admin.com','passwd':'21232f297a57a5a743894a0e4a801fc3'})

        # update default image record to default data
        import base64
        data = {
            'uuid':             uuidgen(),
            'thumbnail':        base64.b64encode(buffer(imaging.defaultThumbnail())),
            'desc':             '系统默认缩略图',
            'status':           1,
        }
        image().update(data, condition)

        # initialize default category record if it is not existing
        if not category().getOne('id', condition):
            category().insert({'id': 1, 'name': '系统分类', 'thumbnail': 1 ,'desc': '系统默认分类，不允许删除!'})

        # initialize default company record if it is not existing
        if not about().getOne('id', {}):
            sqlList = [
                'INSERT INTO about (id, company, address, phoneno, \
                  faxno, postcode, contact, desc, email, logo) VALUES (1, "未知", \
                  "未知", "未知", "未知", "未知", "未知", "未知",  \
                  "unknown@unknown.com", 1);',
            ]
            self.executeSQL(sqlList)

    def GET(self, name):
        # Install: perform installation
        if name == 'freshinstall':
            return self.freshinstall()
        elif name == 'upgrade':
            return self.upgrade()
        elif name == 'reinstall':
            return self.reinstall()
        else:
            return self.notFound()
